1 Imports System.Data.SqlClient
2 Imports System.IO
3 Imports Excel = Microsoft.Office.Interop.Excel
4 Public Class frmStaffCardRecord
5 Sub fillDepartment()
6 Try
7 con = New SqlConnection(cs)
8 con.Open()
9 adp = New SqlDataAdapter()
10 adp.SelectCommand = New SqlCommand("SELECT DepartmentName from Staff,Cards_Staff,Department,Staff_Department where Staff.St_ID=Cards_Staff.StaffID and Staff.St_ID=Staff_Department.StaffID and Department.ID=Staff_Department.DepartmentID", con)
11 ds = New DataSet("ds")
12 adp.Fill(ds)
13 dtable = ds.Tables(0)
14 cmbDepartment.Items.Clear()
15 For Each drow As DataRow In dtable.Rows
16 cmbDepartment.Items.Add(drow(0).ToString())
17 Next
18 Catch ex As Exception
19 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
20 End Try
21 End Sub
22
23 Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click
24 Me.Close()
25 End Sub
26
27
28 Sub Reset()
29 cmbDepartment.SelectedIndex = -1
30 End Sub
31 Private Sub btnReset_Click(sender As System.Object, e As System.EventArgs) Handles btnReset.Click
32 Reset()
33 End Sub
34
35 Private Sub dgw_RowPostPaint(sender As Object, e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
36 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
37 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
38 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
39 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
40 End If
41 Dim b As Brush = SystemBrushes.ControlText
42 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
43
44 End Sub
45
46 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
47 Dim rowsTotal, colsTotal As Short
48 Dim I, j, iC As Short
49 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
50 Dim xlApp As New Excel.Application
51 Try
52 Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
53 Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
54 xlApp.Visible = True
55
56 rowsTotal = dgw.RowCount
57 colsTotal = dgw.Columns.Count - 1
58 With excelWorksheet
59 .Cells.Select()
60 .Cells.Delete()
61 For iC = 0 To colsTotal
62 .Cells(1, iC + 1).Value = dgw.Columns(iC).HeaderText
63 Next
64 For I = 0 To rowsTotal - 1
65 For j = 0 To colsTotal
66 .Cells(I + 2, j + 1).value = dgw.Rows(I).Cells(j).Value
67 Next j
68 Next I
69 .Rows("1:1").Font.FontStyle = "Bold"
70 .Rows("1:1").Font.Size = 12
71
72 .Cells.Columns.AutoFit()
73 .Cells.Select()
74 .Cells.EntireColumn.AutoFit()
75 .Cells(1, 1).Select()
76 End With
77 Catch ex As Exception
78 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
79 Finally
80 'RELEASE ALLOACTED RESOURCES
81 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
82 xlApp = Nothing
83 End Try
84 End Sub
85
86 Private Sub cmbDepartment_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbDepartment.SelectedIndexChanged
87 Try
88 con = New SqlConnection(cs)
89 con.Open()
90 cmd = New SqlCommand("Select RTRIM(Staff.StaffID),RTRIM(StaffName),RTRIM(Cards_Staff.Status) from Staff,Cards_Staff,Department,Staff_Department where Staff.St_ID=Cards_Staff.StaffID and Staff.St_ID=Staff_Department.StaffID and Department.ID=Staff_Department.DepartmentID and DepartmentName=@d1 and Staff.Status='Active' order by StaffName", con)
91 cmd.Parameters.AddWithValue("@d1", cmbDepartment.Text)
92 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
93 dgw.Rows.Clear()
94 While (rdr.Read() = True)
95 dgw.Rows.Add(rdr(0), rdr(1), rdr(2))
96 End While
97 con.Close()
98 Catch ex As Exception
99 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
100 End Try
101 End Sub
102
103
104 Private Sub frmStaffCardRecord_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
105 fillDepartment()
106 End Sub
107 End Class
